SQL and CSV

In [1]:

    

from django.core.management import call_command
from django.db import connection
from graphviz import Source
from IPython.display import Image
import pandas as pd

pd.options.display.max_rows = 10


    

In [2]:

    

%%capture dot
call_command('graph_models', 'music')


    

    




/Users/brian/.virtualenvs/jupyter-django/lib/python3.6/site-packages/ipykernel/__main__.py:1: RemovedInDjango110Warning: OptionParser usage for Django management commands is deprecated, use ArgumentParser instead
  if __name__ == '__main__':

/Users/brian/.virtualenvs/jupyter-django/lib/python3.6/site-packages/django_extensions/management/modelviz.py:307: RemovedInDjango110Warning: render() must be called with a dict, not a Context.
  dot = t.render(c)

In [3]:

    

Source(dot)


    

    
Out[3]:

In [4]:

    

songs = pd.read_sql("""
SELECT
    id AS song_id
    , title AS song_title
    , track AS release_track
    , release_id
FROM music_song
""", connection)

songs.head()


    

    
Out[4]:






  

    

      

      
song_id
      
song_title
      
release_track
      
release_id
    
  
  

    

      
0
      
1
      
Sleeper Agent (Just Waking Up)
      
1
      
1
    
    

      
1
      
2
      
Wave Backwards To Massachusetts
      
2
      
1
    
    

      
2
      
3
      
Hallelujah The Hills
      
3
      
1
    
    

      
3
      
4
      
The House Is All Lit Up
      
4
      
1
    
    

      
4
      
5
      
Raise The Flag Of Your Sibling's Favorite Dayd...
      
5
      
1
    
  

In [5]:

    

releases = pd.read_sql("""
SELECT
    id AS release_id
    , title AS release_title
    , date AS release_date
FROM music_release
""", connection)

releases.head()


    

    
Out[5]:






  

    

      

      
release_id
      
release_title
      
release_date
    
  
  

    

      
0
      
1
      
Collective Psychosis Begone
      
2007-06-05
    
    

      
1
      
2
      
Prepare To Qualify EP
      
2008-04-08
    
    

      
2
      
3
      
Colonial Drones
      
2009-09-22
    
    

      
3
      
7
      
No One Knows What Happens Next
      
2012-05-22
    
    

      
4
      
9
      
Portrait Of The Artist As A Young Trashcan
      
2013-05-07
    
  

In [6]:

    

song_releases = songs.merge(releases).set_index('song_id')
song_releases.head()


    

    
Out[6]:






  

    

      

      
song_title
      
release_track
      
release_id
      
release_title
      
release_date
    
    

      
song_id
      

      

      

      

      

    
  
  

    

      
1
      
Sleeper Agent (Just Waking Up)
      
1
      
1
      
Collective Psychosis Begone
      
2007-06-05
    
    

      
2
      
Wave Backwards To Massachusetts
      
2
      
1
      
Collective Psychosis Begone
      
2007-06-05
    
    

      
3
      
Hallelujah The Hills
      
3
      
1
      
Collective Psychosis Begone
      
2007-06-05
    
    

      
4
      
The House Is All Lit Up
      
4
      
1
      
Collective Psychosis Begone
      
2007-06-05
    
    

      
5
      
Raise The Flag Of Your Sibling's Favorite Dayd...
      
5
      
1
      
Collective Psychosis Begone
      
2007-06-05
    
  

In [7]:

    

release_tracks = song_releases.groupby('release_title')['song_title'].count()
release_tracks


    

    
Out[7]:





release_title
A Band Is Something To Figure Out             11
Collective Psychosis Begone                   12
Colonial Drones                               13
Have You Ever Done Something Evil?            12
Juvenile Oratorios EP                          4
Movement Scorekeepers                          7
No One Knows What Happens Next                10
Portrait Of The Artist As A Young Trashcan    21
Name: song_title, dtype: int64

In [8]:

    

release_tracks.describe()


    

    
Out[8]:





count     8.000000
mean     11.250000
std       4.949747
min       4.000000
25%       9.250000
50%      11.500000
75%      12.250000
max      21.000000
Name: song_title, dtype: float64

In [9]:

    

song_releases.to_csv('song-releases.csv')
!head song-releases.csv


    

    




song_id,song_title,release_track,release_id,release_title,release_date
1,Sleeper Agent (Just Waking Up),1,1,Collective Psychosis Begone,2007-06-05
2,Wave Backwards To Massachusetts,2,1,Collective Psychosis Begone,2007-06-05
3,Hallelujah The Hills,3,1,Collective Psychosis Begone,2007-06-05
4,The House Is All Lit Up,4,1,Collective Psychosis Begone,2007-06-05
5,Raise The Flag Of Your Sibling's Favorite Daydream,5,1,Collective Psychosis Begone,2007-06-05
6,(crux of the camera man),6,1,Collective Psychosis Begone,2007-06-05
7,Slow Motion Records Broken At Break Neck Speeds,7,1,Collective Psychosis Begone,2007-06-05
8,Effie's On The Other Side,8,1,Collective Psychosis Begone,2007-06-05
9,The Trap,9,1,Collective Psychosis Begone,2007-06-05

In [10]:

    

pd.read_csv('song-releases.csv', index_col='song_id').head()


    

    
Out[10]:






  

    

      

      
song_title
      
release_track
      
release_id
      
release_title
      
release_date
    
    

      
song_id
      

      

      

      

      

    
  
  

    

      
1
      
Sleeper Agent (Just Waking Up)
      
1
      
1
      
Collective Psychosis Begone
      
2007-06-05
    
    

      
2
      
Wave Backwards To Massachusetts
      
2
      
1
      
Collective Psychosis Begone
      
2007-06-05
    
    

      
3
      
Hallelujah The Hills
      
3
      
1
      
Collective Psychosis Begone
      
2007-06-05
    
    

      
4
      
The House Is All Lit Up
      
4
      
1
      
Collective Psychosis Begone
      
2007-06-05
    
    

      
5
      
Raise The Flag Of Your Sibling's Favorite Dayd...
      
5
      
1
      
Collective Psychosis Begone
      
2007-06-05
    
  

In [ ]: